﻿using ChurchDiary.Common;
using ChurchDiary.DAL.Contract;
using ChurchDiary.DAL.DAL;
using ChurchDiary.TableEntity;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ChurchDiary.DAL.Methods
{
    public class DAL_ChurchWorker : DataAccess, IDALChurchWorker
    {
        #region ChurchWorker
        public DataTable GetChurchWorkerFullDetailsSet(Guid ChurchWorkerId)
        {
            DataTable dt = new DataTable();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchWorker]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ONE);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerId", DbType.Guid, ChurchWorkerId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        dt = SQLServer.ExecuteDataSet(dbCommand).Tables[0];
                        return dt;
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return dt;
        }


        public EntityChurchWorker GetChurchWorkerFullDetails(Guid ChurchWorkerId)
        {
            EntityChurchWorker ChurchWorker = new EntityChurchWorker();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchWorker]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ONE);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerId", DbType.Guid, ChurchWorkerId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchWorker;
                            }
                            while (OReader.Read())
                            {
                                ChurchWorker.ChurchWorkerId = (Guid)OReader["ChurchWorkerId"];
                                ChurchWorker.ChruchWokerRefNo = (Int32)OReader["ChruchWokerRefNo"];
                                ChurchWorker.Email = OReader["Email"].ToString();
                                ChurchWorker.Phone = OReader["Phone"].ToString();
                                ChurchWorker.Initial = OReader["Initial"].ToString();
                                ChurchWorker.AltPhone = OReader["AltPhone"].ToString();
                                ChurchWorker.Photo = OReader["Photo"].ToString();
                                ChurchWorker.IsDeclarted = (bool)OReader["IsDeclarted"];
                                ChurchWorker.IsMarried = (bool)OReader["IsMarried"];
                                ChurchWorker.IsFromWebsite = (bool)OReader["IsFromWebsite"];
                                ChurchWorker.IsHadOrdination = (bool)OReader["IsHadOrdination"];
                                ChurchWorker.IsTakenTraining = (bool)OReader["IsTakenTraining"];
                                ChurchWorker.PlaceOfTraining = OReader["PlaceOfTraining"].ToString();
                                ChurchWorker.Gender = (Int32)OReader["Gender"];
                                ChurchWorker.ChurchWorkerStatusId = (Guid)OReader["ChurchWorkerStatusId"];
                                ChurchWorker.DateOfBirth = OReader["DateOfBirth"].ToString();
                                ChurchWorker.Fathers = OReader["Fathers"].ToString();
                                ChurchWorker.NativePlace = OReader["NativePlace"].ToString();
                                ChurchWorker.DateOfMarriage = OReader["DateOfMarriage"].ToString();
                                ChurchWorker.DateOfSalvation = OReader["DateOfSalvation"].ToString();
                                ChurchWorker.DateOfAnointing = OReader["DateOfAnointing"].ToString();
                                ChurchWorker.DateOfJoiningInMinitry = OReader["DateOfJoiningInMinitry"].ToString();
                                ChurchWorker.DateOfJoiningInOurMinitry = OReader["DateOfJoiningInOurMinitry"].ToString();
                                ChurchWorker.AboutOrdination = OReader["AboutOrdination"].ToString();
                                ChurchWorker.RecomantationLetter = OReader["RecomantationLetter"].ToString();
                                ChurchWorker.Testimony = OReader["Testimony"].ToString();
                                ChurchWorker.Testimonyfile = OReader["Testimonyfile"].ToString();
                                ChurchWorker.ApplicationCopy = OReader["ApplicationCopy"].ToString();
                                ChurchWorker.CreateLogin = (bool)OReader["CreateLogin"];
                                ChurchWorker.YearOfTraining = OReader["YearOfTraining"].ToString();
                                ChurchWorker.FirstName = OReader["FirstName"].ToString();
                                ChurchWorker.LastName = OReader["LastName"].ToString();
                                ChurchWorker.MiddleName = OReader["MiddleName"].ToString();
                                ChurchWorker.DateOfBaptism = OReader["DateOfBaptism"].ToString();
                                ChurchWorker.Education = OReader["Education"].ToString();
                                ChurchWorker.FullName = OReader["FullName"].ToString();
                                ChurchWorker.NameOfLifePartner = OReader["NameOfLifePartner"].ToString();
                                ChurchWorker.Remarks = OReader["Remarks"].ToString();
                                ChurchWorker.TotalCounts = (Int32)OReader["TotalCounts"];
                                ChurchWorker.DateOfApplication = OReader["DateOfApplication"].ToString();
                                ChurchWorker.PlaceOfApplication = OReader["PlaceOfApplication"].ToString();
                                ChurchWorker.ChurchWorkerDesignationId = (Guid)OReader["ChurchWorkerDesignationId"];
                                ChurchWorker.PresentAddress = new EntityChurchWorkerPresentAddress();
                                ChurchWorker.PresentAddress.PresentAddressId = (Guid)OReader["PresentAddressId"];
                                ChurchWorker.PresentAddress.PresentCountryId = (Guid)OReader["PresentCountryId"];
                                ChurchWorker.PresentAddress.PresentState = (Guid)OReader["PresentState"];
                                ChurchWorker.PresentAddress.PresentCity = OReader["PresentCity"].ToString();
                                ChurchWorker.PresentAddress.PresentAddress = OReader["PresentAddress"].ToString();
                                ChurchWorker.PresentAddress.PresentPin = OReader["PresentPin"].ToString();
                            }
                            ChurchWorker.Designation = GetChurchWorkerDesignation(ChurchWorker.ChurchWorkerDesignationId);
                            ChurchWorker.Statuss = GetChurchWorkerStatus(ChurchWorker.ChurchWorkerStatusId);
                            ChurchWorker.Histroy = GetAllChurchWorkerHistroy(ChurchWorker.ChurchWorkerId);
                            ChurchWorker.Knownlanguages = GetAllChurchWorkerKnownlanguages(ChurchWorker.ChurchWorkerId);
                            ChurchWorker.Documents = GetAllChurchWorkerDocuments(ChurchWorker.ChurchWorkerId);
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchWorker;
        }
        public string InsertChurchWorker(EntityChurchWorker ChurchWorker)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchWorker]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@Email", DbType.String, ChurchWorker.Email);
                    SQLServer.AddInParameter(dbCommand, "@Phone", DbType.String, ChurchWorker.Phone);
                    SQLServer.AddInParameter(dbCommand, "@AltPhone", DbType.String, ChurchWorker.AltPhone);
                    SQLServer.AddInParameter(dbCommand, "@Photo", DbType.String, CommonFunction.UploadImage("~/Uploads/Admin/ChurchWorkers/Photo", ChurchWorker.Photo));
                    SQLServer.AddInParameter(dbCommand, "@IsDeclarted", DbType.Boolean, ChurchWorker.IsDeclarted);
                    SQLServer.AddInParameter(dbCommand, "@IsMarried", DbType.Boolean, ChurchWorker.IsMarried);
                    SQLServer.AddInParameter(dbCommand, "@IsFromWebsite", DbType.Boolean, ChurchWorker.IsFromWebsite);
                    SQLServer.AddInParameter(dbCommand, "@IsHadOrdination", DbType.Boolean, ChurchWorker.IsHadOrdination);
                    SQLServer.AddInParameter(dbCommand, "@IsTakenTraining", DbType.Boolean, ChurchWorker.IsTakenTraining);
                    SQLServer.AddInParameter(dbCommand, "@PlaceOfTraining", DbType.String, ChurchWorker.PlaceOfTraining);
                    SQLServer.AddInParameter(dbCommand, "@Gender", DbType.Int32, ChurchWorker.Gender);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerStatus", DbType.Guid, ChurchWorker.ChurchWorkerStatusId);
                    SQLServer.AddInParameter(dbCommand, "@DateOfBirth", DbType.String, ChurchWorker.DateOfBirth);
                    SQLServer.AddInParameter(dbCommand, "@Fathers", DbType.String, ChurchWorker.Fathers);
                    SQLServer.AddInParameter(dbCommand, "@NativePlace", DbType.String, ChurchWorker.NativePlace);
                    SQLServer.AddInParameter(dbCommand, "@DateOfMarriage", DbType.String, ChurchWorker.DateOfMarriage);
                    SQLServer.AddInParameter(dbCommand, "@DateOfSalvation", DbType.String, ChurchWorker.DateOfSalvation);
                    SQLServer.AddInParameter(dbCommand, "@DateOfAnointing", DbType.String, ChurchWorker.DateOfAnointing);
                    SQLServer.AddInParameter(dbCommand, "@DateOfJoiningInMinitry", DbType.String, ChurchWorker.DateOfJoiningInMinitry);
                    SQLServer.AddInParameter(dbCommand, "@DateOfJoiningInOurMinitry", DbType.String, ChurchWorker.DateOfJoiningInOurMinitry);
                    SQLServer.AddInParameter(dbCommand, "@DateOfBaptism", DbType.String, ChurchWorker.DateOfBaptism);
                    SQLServer.AddInParameter(dbCommand, "@AboutOrdination", DbType.String, ChurchWorker.AboutOrdination);
                    SQLServer.AddInParameter(dbCommand, "@RecomantationLetter", DbType.String, ChurchWorker.RecomantationLetter);
                    SQLServer.AddInParameter(dbCommand, "@Testimony", DbType.String, ChurchWorker.Testimony);
                    SQLServer.AddInParameter(dbCommand, "@Testimonyfile", DbType.String, CommonFunction.UploadImage("~/Uploads/Admin/ChurchWorkers/Testimonyfile", ChurchWorker.Photo));
                    SQLServer.AddInParameter(dbCommand, "@ApplicationCopy", DbType.String, CommonFunction.UploadImage("~/Uploads/Admin/ChurchWorkers/ApplicationCopy", ChurchWorker.ApplicationCopy));
                    SQLServer.AddInParameter(dbCommand, "@CreateLogin", DbType.Boolean, ChurchWorker.CreateLogin);
                    SQLServer.AddInParameter(dbCommand, "@by", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@IpAddress", DbType.String, ChurchWorker.IpAddress);
                    SQLServer.AddInParameter(dbCommand, "@YearOfTraining", DbType.String, ChurchWorker.YearOfTraining);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerDesignationId", DbType.Guid, ChurchWorker.ChurchWorkerDesignationId);
                    SQLServer.AddInParameter(dbCommand, "@Nametitle", DbType.Guid, ChurchWorker.NameTitleId);
                    SQLServer.AddInParameter(dbCommand, "@FirstName", DbType.String, ChurchWorker.FirstName);
                    SQLServer.AddInParameter(dbCommand, "@MiddleName", DbType.String, ChurchWorker.MiddleName);
                    SQLServer.AddInParameter(dbCommand, "@LastName", DbType.String, ChurchWorker.LastName);
                    SQLServer.AddInParameter(dbCommand, "@Education", DbType.String, ChurchWorker.Education);
                    SQLServer.AddInParameter(dbCommand, "@PresentCountryId", DbType.Guid, ChurchWorker.PresentAddress.PresentCountryId);
                    SQLServer.AddInParameter(dbCommand, "@PresentState", DbType.Guid, ChurchWorker.PresentAddress.PresentState);
                    SQLServer.AddInParameter(dbCommand, "@PresentCity", DbType.String, ChurchWorker.PresentAddress.PresentCity);
                    SQLServer.AddInParameter(dbCommand, "@PresentAddress", DbType.String, ChurchWorker.PresentAddress.PresentAddress);
                    SQLServer.AddInParameter(dbCommand, "@PresentPin", DbType.String, ChurchWorker.PresentAddress.PresentPin);
                    SQLServer.AddInParameter(dbCommand, "@NameOfLifePartner", DbType.String, ChurchWorker.NameOfLifePartner);
                    SQLServer.AddInParameter(dbCommand, "@PlaceOfApplication", DbType.String, ChurchWorker.DateOfApplication);
                    SQLServer.AddInParameter(dbCommand, "@DateOfApplication", DbType.String, ChurchWorker.PlaceOfApplication);
                    SQLServer.AddInParameter(dbCommand, "@Remarks", DbType.String, ChurchWorker.Remarks);
                    SQLServer.AddInParameter(dbCommand, "@Initial", DbType.String, ChurchWorker.Initial);
                    SQLServer.AddInParameter(dbCommand, "@FullName", DbType.String, ChurchWorker.Initial + '.' + ChurchWorker.FirstName + ' ' + ChurchWorker.MiddleName + ' ' + ChurchWorker.LastName);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        
    

        public string UpdateChurchWorker(EntityChurchWorker ChurchWorker)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchWorker]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerId", DbType.Guid, ChurchWorker.ChurchWorkerId);
                    SQLServer.AddInParameter(dbCommand, "@Email", DbType.String, ChurchWorker.Email);
                    SQLServer.AddInParameter(dbCommand, "@Phone", DbType.String, ChurchWorker.Phone);
                    SQLServer.AddInParameter(dbCommand, "@AltPhone", DbType.String, ChurchWorker.AltPhone);
                    SQLServer.AddInParameter(dbCommand, "@Photo", DbType.String, CommonFunction.UploadImage("~/Uploads/Admin/ChurchWorkers/Photo", ChurchWorker.Photo));
                    SQLServer.AddInParameter(dbCommand, "@IsDeclarted", DbType.Boolean, ChurchWorker.IsDeclarted);
                    SQLServer.AddInParameter(dbCommand, "@IsMarried", DbType.Boolean, ChurchWorker.IsMarried);
                    SQLServer.AddInParameter(dbCommand, "@IsFromWebsite", DbType.Boolean, ChurchWorker.IsFromWebsite);
                    SQLServer.AddInParameter(dbCommand, "@IsHadOrdination", DbType.Boolean, ChurchWorker.IsHadOrdination);
                    SQLServer.AddInParameter(dbCommand, "@IsTakenTraining", DbType.Boolean, ChurchWorker.IsTakenTraining);
                    SQLServer.AddInParameter(dbCommand, "@PlaceOfTraining", DbType.String, ChurchWorker.PlaceOfTraining);
                    SQLServer.AddInParameter(dbCommand, "@Gender", DbType.Int32, ChurchWorker.Gender);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerStatus", DbType.Guid, ChurchWorker.ChurchWorkerStatusId);
                    SQLServer.AddInParameter(dbCommand, "@DateOfBirth", DbType.String, ChurchWorker.DateOfBirth);
                    SQLServer.AddInParameter(dbCommand, "@Fathers", DbType.String, ChurchWorker.Fathers);
                    SQLServer.AddInParameter(dbCommand, "@NativePlace", DbType.String, ChurchWorker.NativePlace);
                    SQLServer.AddInParameter(dbCommand, "@DateOfMarriage", DbType.String, ChurchWorker.DateOfMarriage);
                    SQLServer.AddInParameter(dbCommand, "@DateOfSalvation", DbType.String, ChurchWorker.DateOfSalvation);
                    SQLServer.AddInParameter(dbCommand, "@DateOfAnointing", DbType.String, ChurchWorker.DateOfAnointing);
                    SQLServer.AddInParameter(dbCommand, "@DateOfJoiningInMinitry", DbType.String, ChurchWorker.DateOfJoiningInMinitry);
                    SQLServer.AddInParameter(dbCommand, "@DateOfJoiningInOurMinitry", DbType.String, ChurchWorker.DateOfJoiningInOurMinitry);
                    SQLServer.AddInParameter(dbCommand, "@DateOfBaptism", DbType.String, ChurchWorker.DateOfBaptism);
                    SQLServer.AddInParameter(dbCommand, "@AboutOrdination", DbType.String, ChurchWorker.AboutOrdination);
                    SQLServer.AddInParameter(dbCommand, "@RecomantationLetter", DbType.String, ChurchWorker.RecomantationLetter);
                    SQLServer.AddInParameter(dbCommand, "@Testimony", DbType.String, ChurchWorker.Testimony);
                    SQLServer.AddInParameter(dbCommand, "@Testimonyfile", DbType.String, CommonFunction.UploadImage("~/Uploads/Admin/ChurchWorkers/Testimonyfile", ChurchWorker.Photo));
                    SQLServer.AddInParameter(dbCommand, "@ApplicationCopy", DbType.String, CommonFunction.UploadImage("~/Uploads/Admin/ChurchWorkers/ApplicationCopy", ChurchWorker.ApplicationCopy));
                    SQLServer.AddInParameter(dbCommand, "@CreateLogin", DbType.Boolean, ChurchWorker.CreateLogin);
                    SQLServer.AddInParameter(dbCommand, "@by", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@IpAddress", DbType.String, ChurchWorker.IpAddress);
                    SQLServer.AddInParameter(dbCommand, "@YearOfTraining", DbType.String, ChurchWorker.YearOfTraining);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerDesignationId", DbType.Guid, ChurchWorker.ChurchWorkerDesignationId);
                    SQLServer.AddInParameter(dbCommand, "@Nametitle", DbType.Guid, ChurchWorker.NameTitleId);
                    SQLServer.AddInParameter(dbCommand, "@FirstName", DbType.String, ChurchWorker.FirstName);
                    SQLServer.AddInParameter(dbCommand, "@MiddleName", DbType.String, ChurchWorker.MiddleName);
                    SQLServer.AddInParameter(dbCommand, "@LastName", DbType.String, ChurchWorker.LastName);
                    SQLServer.AddInParameter(dbCommand, "@Education", DbType.String, ChurchWorker.Education);
                    SQLServer.AddInParameter(dbCommand, "@PresentCountryId", DbType.Guid, ChurchWorker.PresentAddress.PresentCountryId);
                    SQLServer.AddInParameter(dbCommand, "@PresentState", DbType.Guid, ChurchWorker.PresentAddress.PresentState);
                    SQLServer.AddInParameter(dbCommand, "@PresentCity", DbType.String, ChurchWorker.PresentAddress.PresentCity);
                    SQLServer.AddInParameter(dbCommand, "@PresentAddress", DbType.String, ChurchWorker.PresentAddress.PresentAddress);
                    SQLServer.AddInParameter(dbCommand, "@PresentPin", DbType.String, ChurchWorker.PresentAddress.PresentPin);
                    SQLServer.AddInParameter(dbCommand, "@NameOfLifePartner", DbType.String, ChurchWorker.NameOfLifePartner);
                    SQLServer.AddInParameter(dbCommand, "@PlaceOfApplication", DbType.String, ChurchWorker.DateOfApplication);
                    SQLServer.AddInParameter(dbCommand, "@DateOfApplication", DbType.String, ChurchWorker.PlaceOfApplication);
                    SQLServer.AddInParameter(dbCommand, "@Remarks", DbType.String, ChurchWorker.Remarks);
                    SQLServer.AddInParameter(dbCommand, "@Initial", DbType.String, ChurchWorker.Initial);
                    SQLServer.AddInParameter(dbCommand, "@FullName", DbType.String, ChurchWorker.Initial + '.' + ChurchWorker.FirstName + ' ' + ChurchWorker.MiddleName + ' ' + ChurchWorker.LastName);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteChurchWorker(Guid ChurchWorkerId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchWorker]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerId", DbType.Guid, ChurchWorkerId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityChurchWorker> GetChurchWorker(int PageNo, string SearchKey, string Sort, string SortMode, int PerPage)
        {
            List<EntityChurchWorker> ChurchWorkers = new List<EntityChurchWorker>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchWorker]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                    SQLServer.AddInParameter(dbCommand, "@PageNumber", DbType.Int32, PageNo);
                    SQLServer.AddInParameter(dbCommand, "@SearchKey", DbType.String, SearchKey);
                    SQLServer.AddInParameter(dbCommand, "@Sort", DbType.String, Sort);
                    SQLServer.AddInParameter(dbCommand, "@SortMode", DbType.String, SortMode);
                    SQLServer.AddInParameter(dbCommand, "@RowsPerPage", DbType.Int32, PerPage);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchWorkers;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchWorker ChurchWorker = new EntityChurchWorker();
                                ChurchWorker.ChurchWorkerId = (Guid)OReader["ChurchWorkerId"];
                                ChurchWorker.ChruchWokerRefNo = (Int32)OReader["ChruchWokerRefNo"];
                                ChurchWorker.Email = OReader["Email"].ToString();
                                ChurchWorker.Phone = OReader["Phone"].ToString();
                                ChurchWorker.Initial = OReader["Initial"].ToString();
                                ChurchWorker.AltPhone = OReader["AltPhone"].ToString();
                                ChurchWorker.Photo = OReader["Photo"].ToString();
                                ChurchWorker.IsDeclarted = (bool)OReader["IsDeclarted"];
                                ChurchWorker.IsMarried = (bool)OReader["IsMarried"];
                                ChurchWorker.IsFromWebsite = (bool)OReader["IsFromWebsite"];
                                ChurchWorker.IsHadOrdination = (bool)OReader["IsHadOrdination"];
                                ChurchWorker.IsTakenTraining = (bool)OReader["IsTakenTraining"];
                                ChurchWorker.PlaceOfTraining = OReader["PlaceOfTraining"].ToString();
                                ChurchWorker.Gender = (Int32)OReader["Gender"];
                                ChurchWorker.ChurchWorkerStatusId = (Guid)OReader["ChurchWorkerStatusId"];
                                ChurchWorker.DateOfBirth = OReader["DateOfBirth"].ToString();
                                ChurchWorker.Fathers = OReader["Fathers"].ToString();
                                ChurchWorker.NativePlace = OReader["NativePlace"].ToString();
                                ChurchWorker.DateOfMarriage = OReader["DateOfMarriage"].ToString();
                                ChurchWorker.DateOfSalvation = OReader["DateOfSalvation"].ToString();
                                ChurchWorker.DateOfAnointing = OReader["DateOfAnointing"].ToString();
                                ChurchWorker.DateOfJoiningInMinitry = OReader["DateOfJoiningInMinitry"].ToString();
                                ChurchWorker.DateOfJoiningInOurMinitry = OReader["DateOfJoiningInOurMinitry"].ToString();
                                ChurchWorker.AboutOrdination = OReader["AboutOrdination"].ToString();
                                ChurchWorker.RecomantationLetter = OReader["RecomantationLetter"].ToString();
                                ChurchWorker.Testimony = OReader["Testimony"].ToString();
                                ChurchWorker.Testimonyfile = OReader["Testimonyfile"].ToString();
                                ChurchWorker.ApplicationCopy = OReader["ApplicationCopy"].ToString();
                                ChurchWorker.CreateLogin = (bool)OReader["CreateLogin"];
                                ChurchWorker.YearOfTraining = OReader["YearOfTraining"].ToString();
                                ChurchWorker.FirstName = OReader["FirstName"].ToString();
                                ChurchWorker.LastName = OReader["LastName"].ToString();
                                ChurchWorker.MiddleName = OReader["MiddleName"].ToString();
                                ChurchWorker.DateOfBaptism = OReader["DateOfBaptism"].ToString();
                                ChurchWorker.Education = OReader["Education"].ToString();
                                ChurchWorker.FullName = OReader["FullName"].ToString();
                                ChurchWorker.NameOfLifePartner = OReader["NameOfLifePartner"].ToString();
                                ChurchWorker.Remarks = OReader["Remarks"].ToString();
                                ChurchWorker.TotalCounts = (Int32)OReader["TotalCounts"];
                                ChurchWorker.DateOfApplication = OReader["DateOfApplication"].ToString();
                                ChurchWorker.PlaceOfApplication = OReader["PlaceOfApplication"].ToString();
                                ChurchWorker.ChurchWorkerDesignationId = (Guid)OReader["ChurchWorkerDesignationId"];
                                ChurchWorker.Designation = new EntityChurchWorkerDesignation();
                                ChurchWorker.Designation.ChurchWorkerDesignationId = (Guid)OReader["ChurchWorkerDesignationId"];
                                ChurchWorker.Designation.ChurchWorkerDesignationName = OReader["ChurchWorkerDesignationName"].ToString();
                                ChurchWorker.Designation.ChurchWorkerDesignationAbrivation = OReader["ChurchWorkerDesignationAbrivation"].ToString();
                                ChurchWorker.PresentAddress = new EntityChurchWorkerPresentAddress();
                                ChurchWorker.PresentAddress.PresentAddressId = (Guid)OReader["PresentAddressId"];
                                ChurchWorker.PresentAddress.PresentCountryId = (Guid)OReader["PresentCountryId"];
                                ChurchWorker.PresentAddress.PresentState = (Guid)OReader["PresentState"];
                                ChurchWorker.PresentAddress.PresentCity = OReader["PresentCity"].ToString();
                                ChurchWorker.PresentAddress.PresentAddress = OReader["PresentAddress"].ToString();
                                ChurchWorker.PresentAddress.PresentPin = OReader["PresentPin"].ToString();

                                ChurchWorkers.Add(ChurchWorker);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchWorkers;
        }
        public List<EntityChurchWorker> GetChurchWorker()
        {
            List<EntityChurchWorker> ChurchWorkers = new List<EntityChurchWorker>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchWorker]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ALL);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchWorkers;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchWorker ChurchWorker = new EntityChurchWorker();
                                ChurchWorker.ChurchWorkerId = (Guid)OReader["ChurchWorkerId"];
                                ChurchWorker.ChruchWokerRefNo = (Int32)OReader["ChruchWokerRefNo"];
                                ChurchWorker.Email = OReader["Email"].ToString();
                                ChurchWorker.Phone = OReader["Phone"].ToString();
                                ChurchWorker.Initial = OReader["Initial"].ToString();
                                ChurchWorker.AltPhone = OReader["AltPhone"].ToString();
                                ChurchWorker.Photo = OReader["Photo"].ToString();
                                ChurchWorker.IsDeclarted = (bool)OReader["IsDeclarted"];
                                ChurchWorker.IsMarried = (bool)OReader["IsMarried"];
                                ChurchWorker.IsFromWebsite = (bool)OReader["IsFromWebsite"];
                                ChurchWorker.IsHadOrdination = (bool)OReader["IsHadOrdination"];
                                ChurchWorker.IsTakenTraining = (bool)OReader["IsTakenTraining"];
                                ChurchWorker.PlaceOfTraining = OReader["PlaceOfTraining"].ToString();
                                ChurchWorker.Gender = (Int32)OReader["Gender"];
                                ChurchWorker.ChurchWorkerStatusId = (Guid)OReader["ChurchWorkerStatusId"];
                                ChurchWorker.DateOfBirth = OReader["DateOfBirth"].ToString();
                                ChurchWorker.Fathers = OReader["Fathers"].ToString();
                                ChurchWorker.NativePlace = OReader["NativePlace"].ToString();
                                ChurchWorker.DateOfMarriage = OReader["DateOfMarriage"].ToString();
                                ChurchWorker.DateOfSalvation = OReader["DateOfSalvation"].ToString();
                                ChurchWorker.DateOfAnointing = OReader["DateOfAnointing"].ToString();
                                ChurchWorker.DateOfJoiningInMinitry = OReader["DateOfJoiningInMinitry"].ToString();
                                ChurchWorker.DateOfJoiningInOurMinitry = OReader["DateOfJoiningInOurMinitry"].ToString();
                                ChurchWorker.AboutOrdination = OReader["AboutOrdination"].ToString();
                                ChurchWorker.RecomantationLetter = OReader["RecomantationLetter"].ToString();
                                ChurchWorker.Testimony = OReader["Testimony"].ToString();
                                ChurchWorker.Testimonyfile = OReader["Testimonyfile"].ToString();
                                ChurchWorker.ApplicationCopy = OReader["ApplicationCopy"].ToString();
                                ChurchWorker.CreateLogin = (bool)OReader["CreateLogin"];
                                ChurchWorker.YearOfTraining = OReader["YearOfTraining"].ToString();
                                ChurchWorker.FirstName = OReader["FirstName"].ToString();
                                ChurchWorker.LastName = OReader["LastName"].ToString();
                                ChurchWorker.MiddleName = OReader["MiddleName"].ToString();
                                ChurchWorker.DateOfBaptism = OReader["DateOfBaptism"].ToString();
                                ChurchWorker.Education = OReader["Education"].ToString();
                                ChurchWorker.FullName = OReader["FullName"].ToString();
                                ChurchWorker.NameOfLifePartner = OReader["NameOfLifePartner"].ToString();
                                ChurchWorker.Remarks = OReader["Remarks"].ToString();
                                ChurchWorker.TotalCounts = (Int32)OReader["TotalCounts"];
                                ChurchWorker.DateOfApplication = OReader["DateOfApplication"].ToString();
                                ChurchWorker.PlaceOfApplication = OReader["PlaceOfApplication"].ToString();
                                ChurchWorker.ChurchWorkerDesignationId = (Guid)OReader["ChurchWorkerDesignationId"];
                                ChurchWorker.Designation = new EntityChurchWorkerDesignation();
                                ChurchWorker.Designation.ChurchWorkerDesignationId = (Guid)OReader["ChurchWorkerDesignationId"];
                                ChurchWorker.Designation.ChurchWorkerDesignationName = OReader["ChurchWorkerDesignationName"].ToString();
                                ChurchWorker.Designation.ChurchWorkerDesignationAbrivation = OReader["ChurchWorkerDesignationAbrivation"].ToString();
                                ChurchWorker.PresentAddress = new EntityChurchWorkerPresentAddress();
                                ChurchWorker.PresentAddress.PresentAddressId = (Guid)OReader["PresentAddressId"];
                                ChurchWorker.PresentAddress.PresentCountryId = (Guid)OReader["PresentCountryId"];
                                ChurchWorker.PresentAddress.PresentState = (Guid)OReader["PresentState"];
                                ChurchWorker.PresentAddress.PresentCity = OReader["PresentCity"].ToString();
                                ChurchWorker.PresentAddress.PresentAddress = OReader["PresentAddress"].ToString();
                                ChurchWorker.PresentAddress.PresentPin = OReader["PresentPin"].ToString();
                                ChurchWorkers.Add(ChurchWorker);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchWorkers;
        }
        public EntityChurchWorker GetChurchWorker(Guid ChurchWorkerId)
        {
            EntityChurchWorker ChurchWorker = new EntityChurchWorker();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchWorker]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.ONE);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerId", DbType.Guid, ChurchWorkerId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchWorker;
                            }
                            while (OReader.Read())
                            {
                                ChurchWorker.ChurchWorkerId = (Guid)OReader["ChurchWorkerId"];
                                ChurchWorker.ChruchWokerRefNo = (Int32)OReader["ChruchWokerRefNo"];
                                ChurchWorker.Email = OReader["Email"].ToString();
                                ChurchWorker.Phone = OReader["Phone"].ToString();
                                ChurchWorker.Initial = OReader["Initial"].ToString();
                                ChurchWorker.AltPhone = OReader["AltPhone"].ToString();
                                ChurchWorker.Photo = OReader["Photo"].ToString();
                                ChurchWorker.IsDeclarted = (bool)OReader["IsDeclarted"];
                                ChurchWorker.IsMarried = (bool)OReader["IsMarried"];
                                ChurchWorker.IsFromWebsite = (bool)OReader["IsFromWebsite"];
                                ChurchWorker.IsHadOrdination = (bool)OReader["IsHadOrdination"];
                                ChurchWorker.IsTakenTraining = (bool)OReader["IsTakenTraining"];
                                ChurchWorker.PlaceOfTraining = OReader["PlaceOfTraining"].ToString();
                                ChurchWorker.Gender = (Int32)OReader["Gender"];
                                ChurchWorker.ChurchWorkerStatusId = (Guid)OReader["ChurchWorkerStatusId"];
                                ChurchWorker.DateOfBirth = OReader["DateOfBirth"].ToString();
                                ChurchWorker.Fathers = OReader["Fathers"].ToString();
                                ChurchWorker.NativePlace = OReader["NativePlace"].ToString();
                                ChurchWorker.DateOfMarriage = OReader["DateOfMarriage"].ToString();
                                ChurchWorker.DateOfSalvation = OReader["DateOfSalvation"].ToString();
                                ChurchWorker.DateOfAnointing = OReader["DateOfAnointing"].ToString();
                                ChurchWorker.DateOfJoiningInMinitry = OReader["DateOfJoiningInMinitry"].ToString();
                                ChurchWorker.DateOfJoiningInOurMinitry = OReader["DateOfJoiningInOurMinitry"].ToString();
                                ChurchWorker.AboutOrdination = OReader["AboutOrdination"].ToString();
                                ChurchWorker.RecomantationLetter = OReader["RecomantationLetter"].ToString();
                                ChurchWorker.Testimony = OReader["Testimony"].ToString();
                                ChurchWorker.Testimonyfile = OReader["Testimonyfile"].ToString();
                                ChurchWorker.ApplicationCopy = OReader["ApplicationCopy"].ToString();
                                ChurchWorker.CreateLogin = (bool)OReader["CreateLogin"];
                                ChurchWorker.YearOfTraining = OReader["YearOfTraining"].ToString();
                                ChurchWorker.FirstName = OReader["FirstName"].ToString();
                                ChurchWorker.LastName = OReader["LastName"].ToString();
                                ChurchWorker.MiddleName = OReader["MiddleName"].ToString();
                                ChurchWorker.DateOfBaptism = OReader["DateOfBaptism"].ToString();
                                ChurchWorker.Education = OReader["Education"].ToString();
                                ChurchWorker.FullName = OReader["FullName"].ToString();
                                ChurchWorker.NameOfLifePartner = OReader["NameOfLifePartner"].ToString();
                                ChurchWorker.Remarks = OReader["Remarks"].ToString();
                                ChurchWorker.TotalCounts = (Int32)OReader["TotalCounts"];
                                ChurchWorker.DateOfApplication = OReader["DateOfApplication"].ToString();
                                ChurchWorker.PlaceOfApplication = OReader["PlaceOfApplication"].ToString();
                                ChurchWorker.ChurchWorkerDesignationId = (Guid)OReader["ChurchWorkerDesignationId"];
                                ChurchWorker.Designation = new EntityChurchWorkerDesignation();
                                ChurchWorker.Designation.ChurchWorkerDesignationId = (Guid)OReader["ChurchWorkerDesignationId"];
                                ChurchWorker.Designation.ChurchWorkerDesignationName = OReader["ChurchWorkerDesignationName"].ToString();
                                ChurchWorker.Designation.ChurchWorkerDesignationAbrivation = OReader["ChurchWorkerDesignationAbrivation"].ToString();
                                ChurchWorker.PresentAddress = new EntityChurchWorkerPresentAddress();
                                ChurchWorker.PresentAddress.PresentAddressId = (Guid)OReader["PresentAddressId"];
                                ChurchWorker.PresentAddress.PresentCountryId = (Guid)OReader["PresentCountryId"];
                                ChurchWorker.PresentAddress.PresentState = (Guid)OReader["PresentState"];
                                ChurchWorker.PresentAddress.PresentCity = OReader["PresentCity"].ToString();
                                ChurchWorker.PresentAddress.PresentAddress = OReader["PresentAddress"].ToString();
                                ChurchWorker.PresentAddress.PresentPin = OReader["PresentPin"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchWorker;
        }
        #endregion
        #region ChurchWorker Family
        public string InsertChurchWorkerfamily(EntityChurchWorkerFamilyDetails ChurchWorkerfamily)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchWorkerFamily]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerId", DbType.Guid, ChurchWorkerfamily.ChurchWorkerId);
                    SQLServer.AddInParameter(dbCommand, "@RelationShipId", DbType.Guid, ChurchWorkerfamily.RelationShipId);
                    SQLServer.AddInParameter(dbCommand, "@Name", DbType.String, ChurchWorkerfamily.Name);
                    SQLServer.AddInParameter(dbCommand, "@Gender", DbType.Int32, ChurchWorkerfamily.Gender);
                    SQLServer.AddInParameter(dbCommand, "@DateOfBirth", DbType.String, ChurchWorkerfamily.DateOfBirth);
                    SQLServer.AddInParameter(dbCommand, "@by", DbType.Guid, Current.ChurchId);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@IpAddress", DbType.String, "ip");
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateChurchWorkerfamily(EntityChurchWorkerFamilyDetails ChurchWorkerfamily)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchWorkerFamily]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerId", DbType.Guid, ChurchWorkerfamily.ChurchWorkerId);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerFamilyId", DbType.Guid, ChurchWorkerfamily.ChurchWorkerFamilyId);
                    SQLServer.AddInParameter(dbCommand, "@RelationShipId", DbType.Guid, ChurchWorkerfamily.RelationShipId);
                    SQLServer.AddInParameter(dbCommand, "@Name", DbType.String, ChurchWorkerfamily.Name);
                    SQLServer.AddInParameter(dbCommand, "@Gender", DbType.Int32, ChurchWorkerfamily.Gender);
                    SQLServer.AddInParameter(dbCommand, "@DateOfBirth", DbType.String, ChurchWorkerfamily.DateOfBirth);
                    SQLServer.AddInParameter(dbCommand, "@by", DbType.Guid, Current.ChurchId);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@IpAddress", DbType.String, "ip");
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteChurchWorkerfamily(Guid ChurchWorkerfamilyId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchWorkerFamily]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerFamilyId", DbType.Guid, ChurchWorkerfamilyId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityChurchWorkerFamilyDetails> GetChurchWorkerFamily(int PageNo, string SearchKey, string Sort, string SortMode, int PerPage, Guid ChurchWorkerId)
        {
            List<EntityChurchWorkerFamilyDetails> ChurchWorkerFamilyDetails = new List<EntityChurchWorkerFamilyDetails>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchWorkerfamily]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                    SQLServer.AddInParameter(dbCommand, "@PageNumber", DbType.Int32, PageNo);
                    SQLServer.AddInParameter(dbCommand, "@SearchKey", DbType.String, SearchKey);
                    SQLServer.AddInParameter(dbCommand, "@Sort", DbType.String, Sort);
                    SQLServer.AddInParameter(dbCommand, "@SortMode", DbType.String, SortMode);
                    SQLServer.AddInParameter(dbCommand, "@RowsPerPage", DbType.Int32, PerPage);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerId", DbType.Guid, ChurchWorkerId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchWorkerFamilyDetails;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchWorkerFamilyDetails ChurchWorkerFamilyDetail = new EntityChurchWorkerFamilyDetails();
                                ChurchWorkerFamilyDetail.ChurchWorkerFamilyId = (Guid)OReader["ChurchWorkerFamilyId"];
                                ChurchWorkerFamilyDetail.ChurchWorkerId = (Guid)OReader["ChurchWorkerId"];
                                ChurchWorkerFamilyDetail.DateOfBirth = OReader["DateOfBirth"].ToString();
                                ChurchWorkerFamilyDetail.Gender = (Int32)OReader["Gender"];
                                ChurchWorkerFamilyDetail.Name = OReader["Name"].ToString();
                                ChurchWorkerFamilyDetail.TotalRecords = (Int32)OReader["TotalRecords"];
                                ChurchWorkerFamilyDetail.RelationShipId = (Guid)OReader["RelationShipId"];
                                ChurchWorkerFamilyDetail.RelationShip = new EntityRelationShip();
                                ChurchWorkerFamilyDetail.RelationShip.RelationShipName = OReader["RelationShipName"].ToString();
                                ChurchWorkerFamilyDetails.Add(ChurchWorkerFamilyDetail);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchWorkerFamilyDetails;
        }
        public EntityChurchWorkerFamilyDetails GetChurchWorkerFamily(Guid ChurchWorkerFamilyId)
        {
            EntityChurchWorkerFamilyDetails ChurchWorkerFamilyDetail = new EntityChurchWorkerFamilyDetails();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchWorkerfamily]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.ONE);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerId", DbType.Guid, ChurchWorkerFamilyId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchWorkerFamilyDetail;
                            }
                            while (OReader.Read())
                            {
                                ChurchWorkerFamilyDetail.ChurchWorkerFamilyId = (Guid)OReader["ChurchWorkerFamilyId"];
                                ChurchWorkerFamilyDetail.ChurchWorkerId = (Guid)OReader["ChurchWorkerId"];
                                ChurchWorkerFamilyDetail.DateOfBirth = OReader["DateOfBirth"].ToString();
                                ChurchWorkerFamilyDetail.Gender = (Int32)OReader["Gender"];
                                ChurchWorkerFamilyDetail.Name = OReader["Name"].ToString();
                                ChurchWorkerFamilyDetail.TotalRecords = (Int32)OReader["TotalRecords"];
                                ChurchWorkerFamilyDetail.RelationShipId = (Guid)OReader["RelationShipId"];
                                ChurchWorkerFamilyDetail.RelationShip = new EntityRelationShip();
                                ChurchWorkerFamilyDetail.RelationShip.RelationShipName = OReader["RelationShipName"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchWorkerFamilyDetail;
        }
        #endregion
        #region ChurchWorker Documents
        public string InsertDocument(EntityDocuments Documents)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Documents]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@DocumentName", DbType.String, Documents.DocumentName);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateDocument(EntityDocuments Document)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Documents]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@DocumentId", DbType.Guid, Document.DocumentId);
                    SQLServer.AddInParameter(dbCommand, "@DocumentName", DbType.String, Document.DocumentName);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteDocument(Guid DocumentId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_Documents]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@DocumentId", DbType.Guid, DocumentId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityDocuments> GetDocuments(int PageNo, string SearchKey,string Sort,string SortMode)
        {
            List<EntityDocuments> Documents = new List<EntityDocuments>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Documents]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                    SQLServer.AddInParameter(dbCommand, "@PageNumber", DbType.Int32, PageNo);
                    SQLServer.AddInParameter(dbCommand, "@SearchKey", DbType.String, SearchKey);
                    SQLServer.AddInParameter(dbCommand, "@Sort", DbType.String, Sort);
                    SQLServer.AddInParameter(dbCommand, "@SortMode", DbType.String, SortMode);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Documents;
                            }
                            while (OReader.Read())
                            {
                                EntityDocuments Document = new EntityDocuments();
                                Document.DocumentId = (Guid)OReader["DocumentId"];
                                Document.DocumentName = OReader["DocumentName"].ToString();
                                Document.TotalCounts = (Int32)OReader["TotalCounts"];
                                Documents.Add(Document);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Documents;
        }
        public List<EntityDocuments> GetDocuments()
        {
            List<EntityDocuments> Documents = new List<EntityDocuments>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Documents]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ALL);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Documents;
                            }
                            while (OReader.Read())
                            {
                                EntityDocuments Document = new EntityDocuments();
                                Document.DocumentId = (Guid)OReader["DocumentId"];
                                Document.DocumentName = OReader["DocumentName"].ToString();
                                Documents.Add(Document);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Documents;
        }
        public EntityDocuments GetDocument(Guid DocumentId)
        {
            EntityDocuments Document = new EntityDocuments();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_Documents]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.ONE);
                    SQLServer.AddInParameter(dbCommand, "@DocumentId", DbType.Guid, DocumentId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Document;
                            }
                            while (OReader.Read())
                            {
                                Document.DocumentId = (Guid)OReader["DocumentId"];
                                Document.DocumentName = OReader["DocumentName"].ToString();
                                Document.TotalCounts = (Int32)OReader["TotalCounts"];
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Document;
        }
        #endregion
        #region ChurchWorker Designation
        public string InsertChurchWorkerDesignation(EntityChurchWorkerDesignation ChurchWorkerDesignation)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchWorkerDesignation]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    //SQLServer.AddInParameter(dbCommand, "@ChurchWorkerDesignationId", DbType.String, Documents.DocumentName);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerDesignationName", DbType.String, ChurchWorkerDesignation.ChurchWorkerDesignationName);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerDesignationAbrivation", DbType.String, ChurchWorkerDesignation.ChurchWorkerDesignationAbrivation);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerDesignationLevel", DbType.Int32, ChurchWorkerDesignation.ChurchWorkerDesignationLevel);
                    SQLServer.AddInParameter(dbCommand, "@Gender", DbType.Int32, ChurchWorkerDesignation.Gender);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@Ip", DbType.String, "ip");
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateChurchWorkerDesignation(EntityChurchWorkerDesignation ChurchWorkerDesignation)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchWorkerDesignation]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerDesignationId", DbType.Guid, ChurchWorkerDesignation.ChurchWorkerDesignationId);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerDesignationName", DbType.String, ChurchWorkerDesignation.ChurchWorkerDesignationName);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerDesignationAbrivation", DbType.String, ChurchWorkerDesignation.ChurchWorkerDesignationAbrivation);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerDesignationLevel", DbType.Int32, ChurchWorkerDesignation.ChurchWorkerDesignationLevel);
                    SQLServer.AddInParameter(dbCommand, "@Gender", DbType.Int32, ChurchWorkerDesignation.Gender);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@Ip", DbType.String, "ip");
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteChurchWorkerDesignation(Guid ChurchWorkerDesignationId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchWorkerDesignation]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerDesignationId", DbType.Guid, ChurchWorkerDesignationId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityChurchWorkerDesignation> GetChurchWorkerDesignation(int PageNo, string SearchKey, string Sort, string SortMode)
        {
            List<EntityChurchWorkerDesignation> ChurchWorkerDesignations = new List<EntityChurchWorkerDesignation>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchWorkerDesignation]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                    SQLServer.AddInParameter(dbCommand, "@PageNumber", DbType.Int32, PageNo);
                    SQLServer.AddInParameter(dbCommand, "@SearchKey", DbType.String, SearchKey);
                    SQLServer.AddInParameter(dbCommand, "@Sort", DbType.String, Sort);
                    SQLServer.AddInParameter(dbCommand, "@SortMode", DbType.String, SortMode);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchWorkerDesignations;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchWorkerDesignation ChurchWorkerDesignation = new EntityChurchWorkerDesignation();
                                ChurchWorkerDesignation.ChurchWorkerDesignationId = (Guid)OReader["ChurchWorkerDesignationId"];
                                ChurchWorkerDesignation.ChurchWorkerDesignationName = OReader["ChurchWorkerDesignationName"].ToString();
                                ChurchWorkerDesignation.ChurchWorkerDesignationAbrivation = OReader["ChurchWorkerDesignationAbrivation"].ToString();
                                ChurchWorkerDesignation.ChurchWorkerDesignationLevel = (Int32)OReader["ChurchWorkerDesignationLevel"];
                                ChurchWorkerDesignation.Gender = (Int32)OReader["Gender"];
                                ChurchWorkerDesignation.TotalRecords = (Int32)OReader["TotalCounts"];
                                ChurchWorkerDesignations.Add(ChurchWorkerDesignation);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchWorkerDesignations;
        }
        public List<EntityChurchWorkerDesignation> GetChurchWorkerDesignation()
        {
            List<EntityChurchWorkerDesignation> ChurchWorkerDesignations = new List<EntityChurchWorkerDesignation>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchWorkerDesignation]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ALL);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchWorkerDesignations;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchWorkerDesignation ChurchWorkerDesignation = new EntityChurchWorkerDesignation();
                                ChurchWorkerDesignation.ChurchWorkerDesignationId = (Guid)OReader["ChurchWorkerDesignationId"];
                                ChurchWorkerDesignation.ChurchWorkerDesignationName = OReader["ChurchWorkerDesignationName"].ToString();
                                ChurchWorkerDesignation.ChurchWorkerDesignationAbrivation = OReader["ChurchWorkerDesignationAbrivation"].ToString();
                                ChurchWorkerDesignation.ChurchWorkerDesignationLevel = (Int32)OReader["ChurchWorkerDesignationLevel"];
                                ChurchWorkerDesignation.Gender = (Int32)OReader["Gender"];
                                ChurchWorkerDesignation.Counts = (Int32)OReader["Counts"];
                                ChurchWorkerDesignations.Add(ChurchWorkerDesignation);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchWorkerDesignations;
        }
        public EntityChurchWorkerDesignation GetChurchWorkerDesignation(Guid ChurchWorkerDesignationId)
        {
            EntityChurchWorkerDesignation ChurchWorkerDesignation = new EntityChurchWorkerDesignation();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchWorkerDesignation]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.ONE);
                    SQLServer.AddInParameter(dbCommand, "@DesignationId", DbType.Guid, ChurchWorkerDesignationId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchWorkerDesignation;
                            }
                            while (OReader.Read())
                            {
                                ChurchWorkerDesignation.ChurchWorkerDesignationId = (Guid)OReader["ChurchWorkerDesignationId"];
                                ChurchWorkerDesignation.ChurchWorkerDesignationName = OReader["ChurchWorkerDesignationName"].ToString();
                                ChurchWorkerDesignation.ChurchWorkerDesignationAbrivation = OReader["ChurchWorkerDesignationAbrivation"].ToString();
                                ChurchWorkerDesignation.ChurchWorkerDesignationLevel = (Int32)OReader["ChurchWorkerDesignationLevel"];
                                ChurchWorkerDesignation.Gender = (Int32)OReader["Gender"];
                                ChurchWorkerDesignation.TotalRecords = (Int32)OReader["TotalCounts"];
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchWorkerDesignation;
        }
        #endregion
        #region ChurchWorker NameTitle
        public string InsertNameTitle(EntityNameTitle NameTitle)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_NameTitle]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@NameDesc", DbType.String, NameTitle.NameDesc);
                    SQLServer.AddInParameter(dbCommand, "@NameTitle", DbType.String, NameTitle.NameTitle);
                    SQLServer.AddInParameter(dbCommand, "@by", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@IpAddress", DbType.String, "");
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateNameTitle(EntityNameTitle NameTitle)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_NameTitle]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@NameTitleId", DbType.Guid, NameTitle.NameTitleId);
                    SQLServer.AddInParameter(dbCommand, "@NameDesc", DbType.String, NameTitle.NameDesc);
                    SQLServer.AddInParameter(dbCommand, "@NameTitle", DbType.String, NameTitle.NameTitle);
                    SQLServer.AddInParameter(dbCommand, "@by", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@IpAddress", DbType.String, "");
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteNameTitle(Guid NameTitleId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_NameTitle]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@NameTitleId", DbType.Guid, NameTitleId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityNameTitle> GetNameTitle(int PageNo, string SearchKey, string Sort, string SortMode)
        {
            List<EntityNameTitle> NameTitles = new List<EntityNameTitle>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_NameTitle]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                    SQLServer.AddInParameter(dbCommand, "@PageNumber", DbType.Int32, PageNo);
                    SQLServer.AddInParameter(dbCommand, "@SearchKey", DbType.String, SearchKey);
                    SQLServer.AddInParameter(dbCommand, "@Sort", DbType.String, Sort);
                    SQLServer.AddInParameter(dbCommand, "@SortMode", DbType.String, SortMode);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return NameTitles;
                            }
                            while (OReader.Read())
                            {
                                EntityNameTitle NameTitle = new EntityNameTitle();
                                NameTitle.NameTitleId = (Guid)OReader["NameTitleId"];
                                NameTitle.NameTitle = OReader["NameTitle"].ToString();
                                NameTitle.NameDesc = OReader["NameDesc"].ToString();
                                NameTitle.TotalCounts = (Int32)OReader["TotalCounts"];
                                NameTitles.Add(NameTitle);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return NameTitles;
        }
        public EntityNameTitle GetNameTitle(Guid NameTitleId)
        {
            EntityNameTitle NameTitle = new EntityNameTitle();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_NameTitle]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.ONE);
                    SQLServer.AddInParameter(dbCommand, "@NameTitleId", DbType.Guid, NameTitleId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return NameTitle;
                            }
                            while (OReader.Read())
                            {
                                NameTitle.NameTitleId = (Guid)OReader["NameTitleId"];
                                NameTitle.NameTitle = OReader["NameTitle"].ToString();
                                NameTitle.NameDesc = OReader["NameDesc"].ToString();
                                NameTitle.TotalCounts = (Int32)OReader["TotalCounts"];
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return NameTitle;
        }
        public List<EntityNameTitle> GetNameTitle()
        {
            List<EntityNameTitle> NameTitles = new List<EntityNameTitle>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_NameTitle]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ALL);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return NameTitles;
                            }
                            while (OReader.Read())
                            {
                                EntityNameTitle NameTitle = new EntityNameTitle();
                                NameTitle.NameTitleId = (Guid)OReader["NameTitleId"];
                                NameTitle.NameTitle = OReader["NameTitle"].ToString();
                                NameTitle.NameDesc = OReader["NameDesc"].ToString();
                                NameTitle.TotalCounts = (Int32)OReader["TotalCounts"];
                                NameTitles.Add(NameTitle);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return NameTitles;
        }
        #endregion
        #region ChurchWorker Status
        public List<EntityChurchWorkerStatus> GetChurchWorkerStatus(int PageNo, string SearchKey, string Sort, string SortMode, int PerPage)
        {
            List<EntityChurchWorkerStatus> ChurchWorkerStatus = new List<EntityChurchWorkerStatus>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchWorkerStatus]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                    SQLServer.AddInParameter(dbCommand, "@PageNumber", DbType.Int32, PageNo);
                    SQLServer.AddInParameter(dbCommand, "@SearchKey", DbType.String, SearchKey);
                    SQLServer.AddInParameter(dbCommand, "@Sort", DbType.String, Sort);
                    SQLServer.AddInParameter(dbCommand, "@SortMode", DbType.String, SortMode);
                    SQLServer.AddInParameter(dbCommand, "@RowsPerPage", DbType.Int32, PerPage);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchWorkerStatus;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchWorkerStatus churchWorkerStatus = new EntityChurchWorkerStatus();
                                churchWorkerStatus.ChurchWorkerStatusId = (Guid)OReader["ChurchWorkerStatusId"];
                                churchWorkerStatus.ChurchWorkerStatusName = OReader["ChurchWorkerStatusName"].ToString();
                                churchWorkerStatus.ChurchWorkerStatusDescription = OReader["ChurchWorkerStatusDescription"].ToString();
                                churchWorkerStatus.TotalRecords = (Int32)OReader["TotalRecords"];
                                ChurchWorkerStatus.Add(churchWorkerStatus);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchWorkerStatus;
        }
        public List<EntityChurchWorkerStatus> GetChurchWorkerStatus()
        {
            List<EntityChurchWorkerStatus> ChurchWorkerStatus = new List<EntityChurchWorkerStatus>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchWorkerStatus]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ALL);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchWorkerStatus;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchWorkerStatus churchWorkerStatus = new EntityChurchWorkerStatus();
                                churchWorkerStatus.ChurchWorkerStatusId = (Guid)OReader["ChurchWorkerStatusId"];
                                churchWorkerStatus.ChurchWorkerStatusName = OReader["ChurchWorkerStatusName"].ToString();
                                churchWorkerStatus.ChurchWorkerStatusDescription = OReader["ChurchWorkerStatusDescription"].ToString();
                                ChurchWorkerStatus.Add(churchWorkerStatus);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchWorkerStatus;
        }
        public EntityChurchWorkerStatus GetChurchWorkerStatus(Guid ChurchWorkerStatusId)
        {
            EntityChurchWorkerStatus ChurchWorkerStatus = new EntityChurchWorkerStatus();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchWorkerStatus]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.ONE);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerStatusId", DbType.Guid, ChurchWorkerStatusId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchWorkerStatus;
                            }
                            while (OReader.Read())
                            {
                                ChurchWorkerStatus.ChurchWorkerStatusId = (Guid)OReader["ChurchWorkerStatusId"];
                                ChurchWorkerStatus.ChurchWorkerStatusName = OReader["ChurchWorkerStatusName"].ToString();
                                ChurchWorkerStatus.ChurchWorkerStatusDescription = OReader["ChurchWorkerStatusDescription"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchWorkerStatus;
        }
        public string InsertChurchWorkerStatus(EntityChurchWorkerStatus ChurchWorkerStatus)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchWorkerStatus]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerStatusName", DbType.String, ChurchWorkerStatus.ChurchWorkerStatusName);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerStatusDescription", DbType.String, ChurchWorkerStatus.ChurchWorkerStatusDescription);
                    SQLServer.AddInParameter(dbCommand, "@IpAddress", DbType.String, ChurchWorkerStatus.IpAddress);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, ChurchWorkerStatus.CreatedBy);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateChurchWorkerStatus(EntityChurchWorkerStatus ChurchWorkerStatus)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchWorkerStatus]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerStatusId", DbType.Guid, ChurchWorkerStatus.ChurchWorkerStatusId);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerStatusName", DbType.String, ChurchWorkerStatus.ChurchWorkerStatusName);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerStatusDescription", DbType.String, ChurchWorkerStatus.ChurchWorkerStatusDescription);
                    SQLServer.AddInParameter(dbCommand, "@IpAddress", DbType.String, ChurchWorkerStatus.IpAddress);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, ChurchWorkerStatus.UpdatedBy);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteChurchWorkerStatus(Guid ChurchWorkerStatusId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchWorkerStatus]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerStatusId", DbType.Guid, ChurchWorkerStatusId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        #endregion
        #region ChurchWorker Jobtitle
        public string InsertChurchWorkerJobtitle(EntityChurchWorkerJobtitle Jobtitle)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchWorkerJobtitle]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@JobtitleId", DbType.Guid, Jobtitle.JobtitleId);
                    SQLServer.AddInParameter(dbCommand, "@PartitionId", DbType.Guid, Jobtitle.PartitionId);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerId", DbType.Guid, Jobtitle.ChurchWorkerId);
                    SQLServer.AddInParameter(dbCommand, "@ChurchId", DbType.Guid, Jobtitle.ChurchId);
                    SQLServer.AddInParameter(dbCommand, "@DateOfOrdination", DbType.String, Jobtitle.DateOfOrdination);
                    SQLServer.AddInParameter(dbCommand, "@Remarks", DbType.String, Jobtitle.Remarks);
                    SQLServer.AddInParameter(dbCommand, "@CurrentStatus", DbType.Int32, Jobtitle.CurrentStatus);
                    SQLServer.AddInParameter(dbCommand, "@MiniterialType", DbType.Int32, Jobtitle.MiniterialType);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@CreateLogin", DbType.Boolean, Jobtitle.ChurchWorker.CreateLogin);
                    SQLServer.AddInParameter(dbCommand, "@EmailAddress", DbType.String, Jobtitle.ChurchWorker.Email);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public List<EntityChurchWorkerJobtitle> GetChurchWorkerJobtitle(int PageNo, string SearchKey, string Sort, string SortMode, int PerPage)
        {
            List<EntityChurchWorkerJobtitle> ChurchWorkerJobtitles = new List<EntityChurchWorkerJobtitle>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchWorkerJobtitle]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                    SQLServer.AddInParameter(dbCommand, "@PageNumber", DbType.Int32, PageNo);
                    SQLServer.AddInParameter(dbCommand, "@SearchKey", DbType.String, SearchKey);
                    SQLServer.AddInParameter(dbCommand, "@Sort", DbType.String, Sort);
                    SQLServer.AddInParameter(dbCommand, "@SortMode", DbType.String, SortMode);
                    SQLServer.AddInParameter(dbCommand, "@RowsPerPage", DbType.Int32, PerPage);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchWorkerJobtitles;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchWorkerJobtitle ChurchWorkerJobtitle = new EntityChurchWorkerJobtitle();
                                ChurchWorkerJobtitle.ChurchWorkerId = (Guid)OReader["ChurchWorkerId"];
                                ChurchWorkerJobtitle.ChurchWorkerJobtitleId = (Guid)OReader["ChurchWorkerJobtitleId"];
                                ChurchWorkerJobtitle.ChurchId = (Guid)OReader["ChurchId"];
                                ChurchWorkerJobtitle.CurrentStatus = (Int32)OReader["CurrentStatus"];
                                ChurchWorkerJobtitle.DateOfOrdination = OReader["DateOfOrdination"].ToString();
                                ChurchWorkerJobtitle.JobtitleId = (Guid)OReader["JobtitleId"];
                                ChurchWorkerJobtitle.MiniterialType = (Int32)OReader["MiniterialType"];
                                ChurchWorkerJobtitle.PartitionId = (Guid)OReader["PartitionId"];
                                ChurchWorkerJobtitle.Remarks = OReader["Remarks"].ToString();
                                ChurchWorkerJobtitle.TotalRecords = (Int32)OReader["TotalRecords"];

                                ChurchWorkerJobtitle.Church = new EntityChurch();
                                ChurchWorkerJobtitle.Church.ChurchName = OReader["ChurchName"].ToString();

                                ChurchWorkerJobtitle.ChurchWorker = new EntityChurchWorker();
                                ChurchWorkerJobtitle.ChurchWorker.FullName = OReader["FullName"].ToString();

                                ChurchWorkerJobtitle.Jobtitle = new EntityJobtitle();
                                ChurchWorkerJobtitle.Jobtitle.JobTitleName = OReader["JobTitleName"].ToString();

                                ChurchWorkerJobtitle.OrganizationPartition = new EnitityOrganizationPartition();
                                ChurchWorkerJobtitle.OrganizationPartition.PartitionName = OReader["PartitionName"].ToString();

                                ChurchWorkerJobtitle.ChurchWorkerStatus = new EntityChurchWorkerStatus();
                                ChurchWorkerJobtitle.ChurchWorkerStatus.ChurchWorkerStatusName = OReader["ChurchWorkerStatusName"].ToString();

                                ChurchWorkerJobtitles.Add(ChurchWorkerJobtitle);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchWorkerJobtitles;
        }
        #endregion
        #region ChurchWorker Document
        public List<EntityChurchWorkerDocuments> GetAllChurchWorkerDocuments(Guid ChurchWorkerId)
        {
            List<EntityChurchWorkerDocuments> Documents = new List<EntityChurchWorkerDocuments>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchWorkerDocuments]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ALL);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerId", DbType.Guid, ChurchWorkerId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Documents;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchWorkerDocuments Document = new EntityChurchWorkerDocuments();
                                Document.ChurchWorkerDocumentsId = (Guid)OReader["ChurchWorkerDocumentsId"];
                                Document.ChurchWorkerId = (Guid)OReader["ChurchWorkerId"];
                                Document.DocumentId = (Guid)OReader["DocumentId"];
                                Document.Documentfile = OReader["Documentfile"].ToString();
                                Document.Description = OReader["Description"].ToString();
                                Document.TotalRecords = (Int32)OReader["TotalRecords"];
                                Document.CreatedOn = (DateTime)OReader["CreatedOn"];
                                Document.Documents = new EntityDocuments();
                                Document.Documents.DocumentName = OReader["DocumentName"].ToString();
                                Documents.Add(Document);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Documents;
        }
        public List<EntityChurchWorkerDocuments> GetChurchWorkerDocuments(int PageNo, string SearchKey, string Sort, string SortMode, int PerPage,Guid ChurchWorkerId)
        {
            List<EntityChurchWorkerDocuments> Documents = new List<EntityChurchWorkerDocuments>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchWorkerDocuments]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                    SQLServer.AddInParameter(dbCommand, "@PageNumber", DbType.Int32, PageNo);
                    SQLServer.AddInParameter(dbCommand, "@SearchKey", DbType.String, SearchKey);
                    SQLServer.AddInParameter(dbCommand, "@Sort", DbType.String, Sort);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerId", DbType.Guid, ChurchWorkerId);
                    SQLServer.AddInParameter(dbCommand, "@SortMode", DbType.String, SortMode);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Documents;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchWorkerDocuments Document = new EntityChurchWorkerDocuments();
                                Document.ChurchWorkerDocumentsId = (Guid)OReader["ChurchWorkerDocumentsId"];
                                Document.ChurchWorkerId = (Guid)OReader["ChurchWorkerId"];
                                Document.DocumentId = (Guid)OReader["DocumentId"];
                                Document.Documentfile = OReader["Documentfile"].ToString();
                                Document.Description = OReader["Description"].ToString();
                                Document.TotalRecords = (Int32)OReader["TotalRecords"];
                                Document.CreatedOn = (DateTime)OReader["CreatedOn"];
                                Document.Documents = new EntityDocuments();
                                Document.Documents.DocumentName = OReader["DocumentName"].ToString();
                                Documents.Add(Document);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Documents;
        }
        public EntityChurchWorkerDocuments GetChurchWorkerDocuments(Guid ChurchWorkerDocumentsId)
        {
            EntityChurchWorkerDocuments Document = new EntityChurchWorkerDocuments();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchWorkerDocuments]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.ONE);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerDocumentsId", DbType.Guid, ChurchWorkerDocumentsId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Document;
                            }
                            while (OReader.Read())
                            {
                                Document.ChurchWorkerDocumentsId = (Guid)OReader["ChurchWorkerDocumentsId"];
                                Document.ChurchWorkerId = (Guid)OReader["ChurchWorkerId"];
                                Document.DocumentId = (Guid)OReader["DocumentId"];
                                Document.Documentfile = OReader["Documentfile"].ToString();
                                Document.Description = OReader["Description"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Document;
        }
        public string InsertChurchWorkerDocuments(EntityChurchWorkerDocuments ChurchWorkerDocuments)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchWorkerDocuments]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@DocumentId", DbType.Guid, ChurchWorkerDocuments.DocumentId);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerId", DbType.Guid, ChurchWorkerDocuments.ChurchWorkerId);
                    SQLServer.AddInParameter(dbCommand, "@Documentfile", DbType.String, CommonFunction.UploadImage("~/Uploads/Admin/ChurchWorkers/Documents", ChurchWorkerDocuments.Documentfile));
                    SQLServer.AddInParameter(dbCommand, "@Description", DbType.String, ChurchWorkerDocuments.Description);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateChurchWorkerDocuments(EntityChurchWorkerDocuments ChurchWorkerDocuments)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchWorkerDocuments]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerDocumentsId", DbType.Guid, ChurchWorkerDocuments.ChurchWorkerDocumentsId);
                    SQLServer.AddInParameter(dbCommand, "@DocumentId", DbType.Guid, ChurchWorkerDocuments.DocumentId);
                    SQLServer.AddInParameter(dbCommand, "@Documentfile", DbType.String, CommonFunction.UploadImage("~/Uploads/Admin/ChurchWorkers/Documents", ChurchWorkerDocuments.Documentfile));
                    SQLServer.AddInParameter(dbCommand, "@Description", DbType.String, ChurchWorkerDocuments.Description);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteChurchWorkerDocuments(Guid ChurchWorkerDocumentsId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchWorkerDocuments]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@DocumentId", DbType.Guid, ChurchWorkerDocumentsId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        #endregion
        #region ChurchWorker Knownlanguages
        public List<EntityChurchWorkerKnownlanguages> GetAllChurchWorkerKnownlanguages(Guid ChurchWorkerId)
        {
            List<EntityChurchWorkerKnownlanguages> WorkerKnownlanguages = new List<EntityChurchWorkerKnownlanguages>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchWorkerknownLanguages]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ALL);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerId", DbType.Guid, ChurchWorkerId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return WorkerKnownlanguages;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchWorkerKnownlanguages WorkerKnownlanguage = new EntityChurchWorkerKnownlanguages();
                                WorkerKnownlanguage.ChurchWorkerKnownlanguagesId = (Guid)OReader["ChurchWorkerKnownlanguagesId"];
                                WorkerKnownlanguage.ChurchWorkerId = (Guid)OReader["ChurchWorkerId"];
                                WorkerKnownlanguage.LanguageId = (Guid)OReader["LanguageId"];
                                WorkerKnownlanguage.All = (bool)OReader["All"];
                                WorkerKnownlanguage.Spoke = (bool)OReader["Spoke"];
                                WorkerKnownlanguage.Write = (bool)OReader["Write"];
                                WorkerKnownlanguage.Read = (bool)OReader["Read"];
                                WorkerKnownlanguage.TotalRecords = (Int32)OReader["TotalRecords"];
                                WorkerKnownlanguage.Language = new EntityLanguage();
                                WorkerKnownlanguage.Language.LanguageId = (Guid)OReader["ChurchWorkerId"];
                                WorkerKnownlanguage.Language.LanguageName = OReader["LanguageName"].ToString();
                                WorkerKnownlanguages.Add(WorkerKnownlanguage);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return WorkerKnownlanguages;
        }
        public List<EntityChurchWorkerKnownlanguages> GetChurchWorkerKnownlanguages(int PageNo, string SearchKey, string Sort, string SortMode, int PerPage, Guid ChurchWorkerId)
        {
            List<EntityChurchWorkerKnownlanguages> WorkerKnownlanguages = new List<EntityChurchWorkerKnownlanguages>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchWorkerknownLanguages]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                    SQLServer.AddInParameter(dbCommand, "@PageNumber", DbType.Int32, PageNo);
                    SQLServer.AddInParameter(dbCommand, "@SearchKey", DbType.String, SearchKey);
                    SQLServer.AddInParameter(dbCommand, "@Sort", DbType.String, Sort);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerId", DbType.Guid, ChurchWorkerId);
                    SQLServer.AddInParameter(dbCommand, "@SortMode", DbType.String, SortMode);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return WorkerKnownlanguages;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchWorkerKnownlanguages WorkerKnownlanguage = new EntityChurchWorkerKnownlanguages();
                                WorkerKnownlanguage.ChurchWorkerKnownlanguagesId = (Guid)OReader["ChurchWorkerKnownlanguagesId"];
                                WorkerKnownlanguage.ChurchWorkerId = (Guid)OReader["ChurchWorkerId"];
                                WorkerKnownlanguage.LanguageId = (Guid)OReader["LanguageId"];
                                WorkerKnownlanguage.All = (bool)OReader["All"];
                                WorkerKnownlanguage.Spoke = (bool)OReader["Spoke"];
                                WorkerKnownlanguage.Write = (bool)OReader["Write"];
                                WorkerKnownlanguage.Read = (bool)OReader["Read"];
                                WorkerKnownlanguage.TotalRecords = (Int32)OReader["TotalRecords"];
                                WorkerKnownlanguage.Language = new EntityLanguage();
                                WorkerKnownlanguage.Language.LanguageId = (Guid)OReader["ChurchWorkerId"];
                                WorkerKnownlanguage.Language.LanguageName = OReader["LanguageName"].ToString();
                                WorkerKnownlanguages.Add(WorkerKnownlanguage);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return WorkerKnownlanguages;
        }
        public EntityChurchWorkerKnownlanguages GetChurchWorkerKnownlanguages(Guid ChurchWorkerKnownlanguagesId)
        {
            EntityChurchWorkerKnownlanguages WorkerKnownlanguage = new EntityChurchWorkerKnownlanguages();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchWorkerknownLanguages]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.ONE);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerKnownlanguagesId", DbType.Guid, ChurchWorkerKnownlanguagesId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return WorkerKnownlanguage;
                            }
                            while (OReader.Read())
                            {
                               
                                WorkerKnownlanguage.ChurchWorkerKnownlanguagesId = (Guid)OReader["ChurchWorkerKnownlanguagesId"];
                                WorkerKnownlanguage.ChurchWorkerId = (Guid)OReader["ChurchWorkerId"];
                                WorkerKnownlanguage.LanguageId = (Guid)OReader["LanguageId"];
                                WorkerKnownlanguage.All = (bool)OReader["All"];
                                WorkerKnownlanguage.Read = (bool)OReader["Read"];
                                WorkerKnownlanguage.Spoke = (bool)OReader["Spoke"];
                                WorkerKnownlanguage.Write = (bool)OReader["Write"];
                                WorkerKnownlanguage.TotalRecords = (Int32)OReader["TotalRecords"];
                                WorkerKnownlanguage.Language = new EntityLanguage();
                                WorkerKnownlanguage.Language.LanguageId = (Guid)OReader["ChurchWorkerId"];
                                WorkerKnownlanguage.Language.LanguageName = OReader["LanguageName"].ToString();
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return WorkerKnownlanguage;
        }
        public string InsertChurchWorkerKnownlanguages(EntityChurchWorkerKnownlanguages ChurchWorkerKnownlanguages)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchWorkerKnownLanguage]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerKnownlanguagesId", DbType.Guid, ChurchWorkerKnownlanguages.ChurchWorkerKnownlanguagesId);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerId", DbType.Guid, ChurchWorkerKnownlanguages.ChurchWorkerId);
                    SQLServer.AddInParameter(dbCommand, "@LanguageId", DbType.Guid, ChurchWorkerKnownlanguages.LanguageId);
                    SQLServer.AddInParameter(dbCommand, "@Read", DbType.Boolean, ChurchWorkerKnownlanguages.Read);
                    SQLServer.AddInParameter(dbCommand, "@Write", DbType.Boolean, ChurchWorkerKnownlanguages.Write);
                    SQLServer.AddInParameter(dbCommand, "@All", DbType.Boolean, ChurchWorkerKnownlanguages.All);
                    SQLServer.AddInParameter(dbCommand, "@Spoke", DbType.Boolean, ChurchWorkerKnownlanguages.Spoke);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateChurchWorkerKnownlanguages(EntityChurchWorkerKnownlanguages ChurchWorkerKnownlanguages)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchWorkerKnownLanguage]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerKnownlanguagesId", DbType.Guid, ChurchWorkerKnownlanguages.ChurchWorkerKnownlanguagesId);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerId", DbType.Guid, ChurchWorkerKnownlanguages.ChurchWorkerId);
                    SQLServer.AddInParameter(dbCommand, "@LanguageId", DbType.Guid, ChurchWorkerKnownlanguages.LanguageId);
                    SQLServer.AddInParameter(dbCommand, "@Read", DbType.Boolean, ChurchWorkerKnownlanguages.Read);
                    SQLServer.AddInParameter(dbCommand, "@Write", DbType.Boolean, ChurchWorkerKnownlanguages.Write);
                    SQLServer.AddInParameter(dbCommand, "@All", DbType.Boolean, ChurchWorkerKnownlanguages.All);
                    SQLServer.AddInParameter(dbCommand, "@Spoke", DbType.Boolean, ChurchWorkerKnownlanguages.Spoke);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteChurchWorkerKnownlanguages(Guid ChurchWorkerKnownlanguagesId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchWorkerKnownLanguage]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerKnownlanguagesId", DbType.Guid, ChurchWorkerKnownlanguagesId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        #endregion
        #region ChurchWorker History
        public List<EntityChurchWorkerHistroy> GetChurchWorkerHistroy(int PageNo, string SearchKey, string Sort, string SortMode, int PerPage, Guid ChurchWorkerId)
        {
            List<EntityChurchWorkerHistroy> Histroies = new List<EntityChurchWorkerHistroy>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchWorkerHistory]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.LIST);
                    SQLServer.AddInParameter(dbCommand, "@PageNumber", DbType.Int32, PageNo);
                    SQLServer.AddInParameter(dbCommand, "@SearchKey", DbType.String, SearchKey);
                    SQLServer.AddInParameter(dbCommand, "@Sort", DbType.String, Sort);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerHistroyId", DbType.Guid, ChurchWorkerId);
                    SQLServer.AddInParameter(dbCommand, "@SortMode", DbType.String, SortMode);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Histroies;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchWorkerHistroy Histroy = new EntityChurchWorkerHistroy();
                                Histroy.ChurchWorkerHistroyId = (Guid)OReader["ChurchWorkerHistroyId"];
                                Histroy.ChurchWorkerId = (Guid)OReader["ChurchWorkerId"];
                                Histroy.CHChurchid = (Guid)OReader["CHChurchid"];
                                Histroy.DateOfTakingCharge = OReader["DateOfTakingCharge"].ToString();
                                Histroy.DateOfTransfer = OReader["DateOfTransfer"].ToString();
                                Histroy.Remarks = OReader["Remarks"].ToString();
                                Histroy.TotalRecords = (Int32)OReader["TotalRecords"];
                                Histroy.Church = new DAL_ChurchDetails().GetChurch(Histroy.CHChurchid);
                                Histroy.ChurchWorker = GetChurchWorker(Histroy.CHChurchid);
                                Histroies.Add(Histroy);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Histroies;
        }
        public List<EntityChurchWorkerHistroy> GetAllChurchWorkerHistroy(Guid ChurchWorkerId)
        {
            List<EntityChurchWorkerHistroy> Histroies = new List<EntityChurchWorkerHistroy>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchWorkerHistory]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, GETMODE.ALL);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerHistroyId", DbType.Guid, ChurchWorkerId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Histroies;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchWorkerHistroy Histroy = new EntityChurchWorkerHistroy();
                                Histroy.ChurchWorkerHistroyId = (Guid)OReader["ChurchWorkerHistroyId"];
                                Histroy.ChurchWorkerId = (Guid)OReader["ChurchWorkerId"];
                                Histroy.CHChurchid = (Guid)OReader["CHChurchid"];
                                Histroy.DateOfTakingCharge = OReader["DateOfTakingCharge"].ToString();
                                Histroy.DateOfTransfer = OReader["DateOfTransfer"].ToString();
                                Histroy.Remarks = OReader["Remarks"].ToString();
                                Histroy.TotalRecords = (Int32)OReader["TotalRecords"];
                                Histroy.Church = new DAL_ChurchDetails().GetChurch(Histroy.CHChurchid);
                                Histroy.ChurchWorker = GetChurchWorker(Histroy.CHChurchid);
                                Histroies.Add(Histroy);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Histroies;
        }
        public EntityChurchWorkerHistroy GetChurchWorkerHistroy(Guid ChurchWorkerHistroyId)
        {
            EntityChurchWorkerHistroy Histroy = new EntityChurchWorkerHistroy();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchWorkerHistory]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.ONE);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerHistroyId", DbType.Guid, ChurchWorkerHistroyId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return Histroy;
                            }
                            while (OReader.Read())
                            {
                                Histroy.ChurchWorkerHistroyId = (Guid)OReader["ChurchWorkerHistroyId"];
                                Histroy.ChurchWorkerId = (Guid)OReader["ChurchWorkerId"];
                                Histroy.CHChurchid = (Guid)OReader["CHChurchid"];
                                Histroy.DateOfTakingCharge = OReader["DateOfTakingCharge"].ToString();
                                Histroy.DateOfTransfer = OReader["DateOfTransfer"].ToString();
                                Histroy.Remarks = OReader["Remarks"].ToString();
                                Histroy.Church = new DAL_ChurchDetails().GetChurch(Histroy.CHChurchid);
                                Histroy.ChurchWorker = GetChurchWorker(Histroy.CHChurchid);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return Histroy;
        }
        public string InsertChurchWorkerHistroy(EntityChurchWorkerHistroy ChurchWorkerHistroy)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchWorkerHistory]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerId", DbType.Guid, ChurchWorkerHistroy.ChurchWorkerId);
                    SQLServer.AddInParameter(dbCommand, "@Churchid", DbType.Guid, ChurchWorkerHistroy.CHChurchid);
                    SQLServer.AddInParameter(dbCommand, "@DateOfTakingCharge", DbType.String, ChurchWorkerHistroy.DateOfTakingCharge);
                    SQLServer.AddInParameter(dbCommand, "@DateOfTransfer", DbType.String, ChurchWorkerHistroy.DateOfTransfer);
                    SQLServer.AddInParameter(dbCommand, "@Remarks", DbType.String, ChurchWorkerHistroy.Remarks);
                    SQLServer.AddInParameter(dbCommand, "@IpAddress", DbType.String, ChurchWorkerHistroy.IpAddress);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string UpdateChurchWorkerHistroy(EntityChurchWorkerHistroy ChurchWorkerHistroy)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchWorkerHistory]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.UPD);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerId", DbType.Guid, ChurchWorkerHistroy.ChurchWorkerId);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerHistroyId", DbType.Guid, ChurchWorkerHistroy.ChurchWorkerHistroyId);
                    SQLServer.AddInParameter(dbCommand, "@Churchid", DbType.Guid, ChurchWorkerHistroy.CHChurchid);
                    SQLServer.AddInParameter(dbCommand, "@DateOfTakingCharge", DbType.String, ChurchWorkerHistroy.DateOfTakingCharge);
                    SQLServer.AddInParameter(dbCommand, "@DateOfTransfer", DbType.String, ChurchWorkerHistroy.DateOfTransfer);
                    SQLServer.AddInParameter(dbCommand, "@Remarks", DbType.String, ChurchWorkerHistroy.Remarks);
                    SQLServer.AddInParameter(dbCommand, "@IpAddress", DbType.String, ChurchWorkerHistroy.IpAddress);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteChurchWorkerHistroy(Guid ChurchWorkerHistroyId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchWorkerHistory]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerHistroyId", DbType.Guid, ChurchWorkerHistroyId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        #endregion
        #region ChurchWorker Ordination
        public List<EntityChurchWorkerOrdinations> GetChurchWorkerOrdination(Guid ChurchWorkerId)
        {
            List<EntityChurchWorkerOrdinations> ChurchWorkerOrdinations = new List<EntityChurchWorkerOrdinations>();
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[GET_ChurchWorkerOrdination]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, "ORDINATION");
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerId", DbType.Guid, ChurchWorkerId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        using (IDataReader OReader = SQLServer.ExecuteReader(dbCommand))
                        {
                            if (OReader == null)
                            {
                                return ChurchWorkerOrdinations;
                            }
                            while (OReader.Read())
                            {
                                EntityChurchWorkerOrdinations ChurchWorkerDesignation = new EntityChurchWorkerOrdinations();
                                ChurchWorkerDesignation.ChurchWorkerDesignationId = (Guid)OReader["ChurchWorkerDesignationId"];
                                if(!string.IsNullOrEmpty(OReader["OrdinationId"].ToString()))
                                {
                                    ChurchWorkerDesignation.OrdinationId = (Guid)OReader["OrdinationId"];
                                }
                                if (!string.IsNullOrEmpty(OReader["OrdinationedBy"].ToString()))
                                {
                                    ChurchWorkerDesignation.OrdinationedBy = (Guid)OReader["OrdinationedBy"];
                                    ChurchWorkerDesignation.ChurchWorker = GetChurchWorker(ChurchWorkerDesignation.OrdinationedBy);
                                }
                                ChurchWorkerDesignation.ChurchWorkerId = ChurchWorkerId;
                                ChurchWorkerDesignation.DateOfOrdination = OReader["DateOfOrdination"].ToString();
                                
                                ChurchWorkerDesignation.Designation = new EntityChurchWorkerDesignation();
                                ChurchWorkerDesignation.Designation.ChurchWorkerDesignationName = OReader["ChurchWorkerDesignationName"].ToString();
                                ChurchWorkerDesignation.Designation.ChurchWorkerDesignationLevel = (Int32)OReader["ChurchWorkerDesignationLevel"];
                                ChurchWorkerDesignation.Designation.ChurchWorkerDesignationAbrivation = OReader["ChurchWorkerDesignationAbrivation"].ToString();
                                ChurchWorkerOrdinations.Add(ChurchWorkerDesignation);
                            }
                        }
                    }
                }
            }
            catch (Exception Ex)
            {
            }
            return ChurchWorkerOrdinations;
        }
        public string InsertChurchWorkerOrdinations(EntityChurchWorkerOrdinations Ordinations)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchWorkerOrdination]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.INS);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerDesignationId", DbType.Guid, Ordinations.ChurchWorkerDesignationId);
                    SQLServer.AddInParameter(dbCommand, "@ChurchWorkerId", DbType.Guid, Ordinations.ChurchWorkerId);
                    SQLServer.AddInParameter(dbCommand, "@OrdinationedBy", DbType.Guid, Ordinations.OrdinationedBy);
                    SQLServer.AddInParameter(dbCommand, "@DateOfOrdination", DbType.String, Ordinations.DateOfOrdination);
                    SQLServer.AddInParameter(dbCommand, "@By", DbType.Guid, Current.Userid);
                    SQLServer.AddInParameter(dbCommand, "@On", DbType.DateTime, DateTime.Now);
                    SQLServer.AddInParameter(dbCommand, "@Ip", DbType.String, "ip");
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        public string DeleteChurchWorkerOrdinations(Guid OrdinationId)
        {
            string IResult = "SME";
            try
            {
                using (DbCommand dbCommand = SQLServer.GetStoredProcCommand("[dbo].[IDU_ChurchWorkerOrdination]"))
                {
                    SQLServer.AddInParameter(dbCommand, "@Mode", DbType.String, eMode.DEL);
                    SQLServer.AddInParameter(dbCommand, "@OrdinationId", DbType.Guid, OrdinationId);
                    using (DbConnection con = SQLServer.CreateConnection())
                    {
                        IResult = SQLServer.ExecuteScalar(dbCommand).ToString();
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return IResult;
        }
        #endregion

    }
}
